﻿CREATE TABLE core.ErrorCodes
(
	PK_ErrorCode INT NOT NULL IDENTITY,
	ErrorCode INT NOT NULL,
	ErrorIdent VARCHAR(256) NOT NULL,
	ErrorText NVARCHAR(1024) NOT NULL,
	LanguageID INT NOT NULL
) ON [FG_CORE]
GO

ALTER TABLE core.ErrorCodes
	ADD CONSTRAINT PK_ErrorCode
		PRIMARY KEY(PK_ErrorCode) ON [FG_INDEXES]
GO

ALTER TABLE core.ErrorCodes
	ADD CONSTRAINT UNIQUE_core_ErrorCodes_ErrorCode_LanguageID
		UNIQUE(ErrorCode, LanguageID)
GO

CREATE FUNCTION [core].[fn_tbl_GetErrorCode]
(
	@ErrorIdent VARCHAR(256),
	@LanguageID INT
)
RETURNS @ErrorEntry TABLE(ErrorCode INT, ErrorText NVARCHAR(1024))
AS
BEGIN
	INSERT INTO @ErrorEntry
	SELECT e.ErrorCode, e.ErrorText FROM core.ErrorCodes e
		WHERE	e.ErrorIdent = @ErrorIdent
		AND		e.LanguageID = @LanguageID
	
	RETURN;
END
GO

CREATE PROCEDURE core.SaveLogging
	@LogDate DATETIME = GETDATE,
	@LogType INT,
	@VersionNumber VARCHAR(25),
	@CreatedBy NVARCHAR(256),
	@CreatedFromSource NVARCHAR(256),
	@ErrorCode INT = NULL,
	@LanguageID INT = NULL,
	@SessionID VARCHAR(50) = NULL
AS
BEGIN
	/* SSet the values in local variables */
	DECLARE
		@v_LogDate DATETIME = @LogDate,
		@v_LogType INT = @LogType,
		@v_VersionNumber VARCHAR(25) = @VersionNumber,
		@v_CreatedBy NVARCHAR(256) = @CreatedBy,
		@v_CreatedFromSource NVARCHAR(256) = @CreatedFromSource,
		@v_LanguageID INT = @LanguageID,
		@v_SessionID VARCHAR(50) = @SessionID,
		
		@v_currentVersionNumber VARCHAR(25)
	
	/* Error variables */
	DECLARE
		@v_errorCode INT = @ErrorCode,
		@v_errorText NVARCHAR(1024)
	
	/* if @LogDate is null, then set the current date timestamp */
	IF @v_LogDate IS NULL
	BEGIN
		SET @v_LogDate = GETDATE()
	END
	
	/* if LanguageID is null, then set it to default */
	IF @v_LanguageID IS NULL
	BEGIN
		SET @v_LanguageID = 1031 /* ??? from Settings-Table ??? */
	END
	
	/* if the logtype is null */
	IF @v_LogType IS NULL
	BEGIN
		SELECT @v_errorCode = error.ErrorCode, @v_errorText = error.ErrorText FROM core.fn_tbl_GetErrorCode('CORE_LOGGING_LOGTYPE_IS_NULL', @v_LanguageID) error
	END
	
	/* if the logtype is not between 0 and 3 */
	IF @v_LogType NOT BETWEEN 0 AND 3
	BEGIN
		SELECT @v_errorCode = error.ErrorCode, @v_errorText = error.ErrorText FROM core.fn_tbl_GetErrorCode('CORE_LOGGING_LOGTYPE_NOT_VALID', @v_LanguageID) error
	END
	
	/* If the given version not the current version */
	IF core.fn_IsCorrectVersion(@v_VersionNumber) = 0
	BEGIN
		SELECT @v_errorCode = error.ErrorCode, @v_errorText = error.ErrorText FROM core.fn_tbl_GetErrorCode('CORE_LOGGING_INCORRECT_VERSION', @v_LanguageID) error
		SELECT @v_currentVersionNumber = core.fn_GetCurrentVersion()
		
		INSERT INTO core.Logging(LogDate, LogType, VersionNumber, SessionID, ErrorCode, LogMessage, CreatedAt, CreatedBy, CreatedFromSource)
		SELECT @v_LogDate, 3, @v_currentVersionNumber, @v_SessionID, @v_errorCode, @v_errorText, GETDATE(), @v_CreatedBy, @v_CreatedFromSource
	END
	
	INSERT INTO core.Logging(LogDate, LogType, VersionNumber, SessionID, ErrorCode, LogMessage, CreatedAt, CreatedBy, CreatedFromSource)
	SELECT @v_LogDate, @v_LogType, @v_VersionNumber, @v_SessionID, @v_errorCode, @v_errorText, GETDATE(), @v_CreatedBy, @v_CreatedFromSource
END